package Model.SQL;

import java.util.*;
import java.sql.*;

public class DataAccessHelperBase {
    protected String tableName;
    private Statement stmt;
    protected Connection conn;
    
    public DataAccessHelperBase() {}
    
    public void customize(String tableName)
    {
        this.tableName = tableName;
    }
    
    protected ResultSet getData(String where)
    {
        this.stmt = null;
        ResultSet rs = null;
        int total = 0;
        conn = Connector.getConnector();
        if(where.length() > 0)
            where = "WHERE " + where;
        
        try 
        {
            this.stmt = conn.createStatement();
            System.out.println("SELECT count(*) as total FROM " + this.tableName + " " + where);
            rs = this.stmt.executeQuery("SELECT count(*) as total FROM " + this.tableName + " " + where );
            rs.next();
            total = rs.getInt("total");   
            if(total <= 0)
                return null;
            rs = this.stmt.executeQuery("SELECT * FROM " + this.tableName + " " + where );
            return rs;
        } 
        catch(SQLException e)
        {
            System.out.println("SQLException: " + e.getMessage());
        }
        return null;
    }
    
    protected int insertRecord(String[] values)
    {
        
        this.stmt = null;
        ResultSet rs = null;
        String query = "";
        conn = Connector.getConnector();
        
        query = "INSERT INTO " + this.tableName + " VALUES( ";
        
        for(int i=0; i<values.length; i++)
            query += values[i] + ", ";        
        query = query.substring(0, query.length()-2);
        query += ")";
        
        try 
        {
            this.stmt = conn.createStatement();
            return this.stmt.executeUpdate(query);
        } 
        catch(SQLException e)
        {
            System.out.println("SQLException: " + e.getMessage());
            return 0;
        }
        finally 
        {
            if (rs != null) 
            {
                try 
                {
                    rs.close();
                } catch (SQLException sqlEx) { }
                rs = null;
            }
            if (this.stmt != null) 
            {
                try 
                {
                    stmt.close();
                } catch (SQLException sqlEx) { }
                stmt = null;
            }        
        }
    }
    
    protected int updateRecord(String id, String[] values)
    {
        this.stmt = null;
        ResultSet rs = null;
        conn = Connector.getConnector();
        String where = "";
        String update = "";
        
        where += "WHERE " + id;
        for(int i=0; i<values.length; i++)
            update += values[i] + " , ";        
        update = update.substring(0, update.length()-2);
        
        System.out.println("UPDATE " + this.tableName + " SET " + update + where);
        try 
        {
            this.stmt = conn.createStatement();
            return this.stmt.executeUpdate("UPDATE " + this.tableName + " SET " + update + where);
        } 
        catch(SQLException e)
        {
            System.out.println("SQLException: " + e.getMessage());
            return 0;
        }
        finally 
        {
            if (rs != null) 
            {
                try 
                {
                    rs.close();
                } catch (SQLException sqlEx) { }
                rs = null;
            }
            if (this.stmt != null) 
            {
                try 
                {
                    stmt.close();
                } catch (SQLException sqlEx) { }
                stmt = null;
            }
        }
    }
    
    protected int deleteRecord(String id)
    {
        this.stmt = null;
        ResultSet rs = null;
        conn = Connector.getConnector();
        String where = id;
          
        System.out.println("DELETE FROM " + this.tableName + " WHERE " + where);
        try 
        {
            this.stmt = conn.createStatement();
            return this.stmt.executeUpdate("DELETE FROM " + this.tableName + " WHERE " + where);
        } 
        catch(SQLException e)
        {
            System.out.println("SQLException: " + e.getMessage());
            return 0;
        }
        finally 
        {
            if (rs != null) 
            {
                try 
                {
                    rs.close();
                } catch (SQLException sqlEx) { }
                rs = null;
            }
            if (this.stmt != null) 
            {
                try 
                {
                    this.stmt.close();
                } catch (SQLException sqlEx) { }
                this.stmt = null;
            }
        }
    }
}
